﻿using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace ExcelImport
{
    /// <summary>
    /// Excel 助手
    /// 
    /// 分组数据仅支持 Dictionary 类型
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class Adv_ExcelImportHelper<T> : BaseExcelImport<T> where T : class
    {
        /// <summary>
        /// 列头与列序号的对应字典
        /// </summary>
        private Dictionary<string, int> _headerPath_to_columnIndex = new Dictionary<string, int>();

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="path">导出文件的路径</param>
        /// <param name="datas">需要导出的数据</param>
        /// <returns>能否正确执行</returns>
        /// <exception cref="FileNotFoundException"></exception>
        /// <exception cref="IOException"></exception>
        public override bool ExportData(string path, IEnumerable<T> datas)
        {
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                bool xlsx = string.Equals(Path.GetExtension(path).ToLower(), ".xlsx");
                _workbook = GetWorkbookBySuffix(null, xlsx);
                _sheet = _workbook.CreateSheet(SheetName);

                WriteDatasToSheet(datas);

                _workbook.Write(fs);
            }

            return true;
        }

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="path">导出模板的路径</param>
        /// <returns>是否顺利执行</returns>
        /// <exception cref="FileNotFoundException"></exception>
        /// <exception cref="IOException"></exception>
        public override bool ExportTemplate(string path)
        {
            using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
            {
                bool xlsx = string.Equals(Path.GetExtension(path).ToLower(), ".xlsx");
                _workbook = GetWorkbookBySuffix(null, xlsx);
                _sheet = _workbook.CreateSheet(SheetName);

                List<ExcelHeader> headers = GenExcelHeaders(typeof(T));
                WriteHeadersToSheet(headers, 0, 0);

                _workbook.Write(fs);
            }

            return true;
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="path">导入的文件路径</param>
        /// <returns>导入的数据</returns>
        public override IEnumerable<T> Import(string path)
        {
            List<T> result = new List<T>();
            try
            {
                using (var fs = new FileStream(path, FileMode.Open, FileAccess.Read))
                {
                    bool xlsx = string.Equals(Path.GetExtension(path).ToLower(), ".xlsx");
                    _workbook = GetWorkbookBySuffix(fs, xlsx);

                    // 若通过表格名称找不到 sheet，则默认导入第一张工作表
                    _sheet = _workbook.GetSheet(SheetName) ?? _workbook.GetSheetAt(0);

                var lastCol = GetTotalHeaderLastColumn();
                var headers = GenExcelHeaders(0, lastCol, 0);

                _headerPath_to_columnIndex = GenDicByHeader(headers);

                var type = typeof(T);

                    if (ExcelHeader_Fit_ClassProperty())
                    {
                        int firstDataRowNum = headers.Max(h => h.Height);

                        for (int i = firstDataRowNum; i <= _sheet.LastRowNum; i++)
                        {
                            _row = _sheet.GetRow(i);
                            if (_row == null)
                                continue;
                            var obj = GenData(type, null);
                            result.Add(obj as T);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                result = null;
                Console.WriteLine($"导入信号源失败 {ex.Message}");
            }

            return result;
        }

        /// <summary>
        /// 根据某个类型生成 Excel 表格的头部
        /// </summary>
        /// <param name="t">类型</param>
        /// <returns>excel 表格的头部</returns>
        private List<ExcelHeader> GenExcelHeaders(Type t)
        {
            var result = new List<ExcelHeader>();

            foreach (var prop in t.GetProperties())
            {
                if (Attribute.GetCustomAttribute(prop, typeof(ExcelHeaderAttribute)) is ExcelHeaderAttribute attr)
                {
                    ExcelHeader parent = new ExcelHeader()
                    {
                        HeaderText = attr.Header,
                        PropertyName = prop.Name,
                        Comment = attr.Comment,
                        ValidationData = attr.ValidationData
                    };

                    //是Dictionary的分组
                    if (string.Equals(prop.PropertyType.Name, "Dictionary`2"))
                    {
                        var genArgTypes = prop.PropertyType.GetGenericArguments();

                        if (genArgTypes[0] == typeof(string))
                        {
                            if (genArgTypes[1] == typeof(string) || genArgTypes[1].IsValueType)
                            {
                                var child = new ExcelHeader()
                                {
                                    HeaderText = "1",
                                };
                                var child2 = new ExcelHeader()
                                {
                                    HeaderText = "2",
                                };
                                parent.Children.AddRange(new[] { child, child2 });
                            }

                            else
                            {
                                var child = new ExcelHeader() { HeaderText = "1" };
                                var child2 = new ExcelHeader() { HeaderText = "2" };
                                child.Children.AddRange(GenExcelHeaders(genArgTypes[1]));
                                child2.Children.AddRange(GenExcelHeaders(genArgTypes[1]));
                                parent.Children.AddRange(new[] { child, child2 });
                            }
                        }
                    }
                    else
                    {
                        if (prop.PropertyType != typeof(string) && !prop.PropertyType.IsValueType)
                        {
                            parent.Children.AddRange(GenExcelHeaders(prop.PropertyType));
                        }
                    }

                    result.Add(parent);
                }

            }

            return result;
        }

        /// <summary>
        /// 根据某数据集合，生成 excel 表格的头部
        /// </summary>
        /// <param name="datas">数据集合</param>
        /// <param name="firstCol">起始列序号</param>
        /// <returns>excel 表格的头部</returns>
        private List<ExcelHeader> GenExcelHeaders(IEnumerable datas, int firstCol = 0)
        {
            var result = new List<ExcelHeader>();

            var types = datas.GetType().GetGenericArguments();

            foreach (var prop in types[0].GetProperties())
            {
                if (Attribute.GetCustomAttribute(prop, typeof(ExcelHeaderAttribute)) is ExcelHeaderAttribute attr)
                {
                    ExcelHeader parent = new ExcelHeader()
                    {
                        HeaderText = attr.Header,
                        FirstCol = firstCol,
                        Comment = attr.Comment,
                        ValidationData = attr.ValidationData
                    };

                    IList allValues = null;

                    if (prop.PropertyType.Name == "Dictionary`2")
                    {
                        var genArgTypes = prop.PropertyType.GetGenericArguments();
                        var genType = typeof(List<>).MakeGenericType(genArgTypes[1]);
                        allValues = Activator.CreateInstance(genType) as IList;
                        List<string> allKeys = new List<string>();


                        foreach (var data in datas)
                        {
                            var dict = prop.GetValue(data, null) as IDictionary;
                            foreach (DictionaryEntry item in dict)
                            {
                                if (!allKeys.Contains(item.Key.ToString()))
                                {
                                    allKeys.Add(item.Key.ToString());
                                }
                                allValues.Add(item.Value);

                            }
                        }

                        var children = allKeys.Select(key => new ExcelHeader() { HeaderText = key }).ToList();
                        parent.Children.AddRange(children);
                        if (genArgTypes[1] != typeof(string))
                        {
                            var grandChildren = GenExcelHeaders(allValues);

                            int index = firstCol;
                            for (int i = 0; i < children.Count; i++)
                            {
                                children[i].FirstCol = index;

                                foreach (var grandChild in grandChildren.Select(g => g.Clone() as ExcelHeader))
                                {
                                    grandChild.FirstCol += index;
                                    grandChild.LastCol += grandChild.Width - 1;
                                    children[i].Children.Add(grandChild);
                                    children[i].LastCol += children[i].Width - 1;
                                }

                                index += children[i].Width;

                            }
                        }
                        else
                        {
                            for (int i = 0; i < children.Count; i++)
                            {
                                children[i].FirstCol = i + firstCol;
                            }
                        }
                    }
                    else if (prop.PropertyType != typeof(string) && !prop.PropertyType.IsValueType)
                    {
                        var genType = typeof(List<>).MakeGenericType(prop.PropertyType);
                        allValues = Activator.CreateInstance(genType) as IList;
                        foreach (var data in datas)
                        {
                            var subObj = prop.GetValue(data, null);
                            allValues.Add(subObj);
                        }
                        parent.Children.AddRange(GenExcelHeaders(allValues, firstCol));
                    }

                    parent.LeftJustified();
                    firstCol += parent.Width;
                    result.Add(parent);
                }
            }

            return result;

        }

        /// <summary>
        /// 根据位置，生成 excel 表格的列头
        /// </summary>
        /// <remarks>
        /// 因为头部可能存在合并单元格，所以其实存在开始列、结束列和行数的概念
        /// </remarks>
        /// <param name="firstCol">开始列</param>
        /// <param name="lastCol">结束列</param>
        /// <param name="rowNum">行数</param>
        /// <returns>excel 表格的列头</returns>
        private List<ExcelHeader> GenExcelHeaders(int firstCol, int lastCol, int rowNum)
        {
            var result = new List<ExcelHeader>();
            var row = _sheet.GetRow(rowNum);
            for (int i = firstCol; i <= lastCol; ++i)
            {
                var cell = row.GetCell(i);
                var head = cell.CellType == CellType.String ? cell.StringCellValue : cell.NumericCellValue.ToString();
                if (cell.IsMergedCell)
                {
                    if (string.IsNullOrWhiteSpace(head))
                    {
                        continue;
                    }
                    else
                    {
                        var item = new ExcelHeader() { HeaderText = head };
                        result.Add(item);
                        item.FirstCol = i;
                        item.LastCol = i;

                        int j = i + 1;
                        while (j <= lastCol)
                        {
                            cell = row.GetCell(j);
                            if (cell == null || cell.CellType == CellType.Blank)
                            {

                                item.LastCol++;
                                j++;
                                i++;
                            }
                            else
                            {
                                break;
                            }
                        }

                        if (item.FirstCol != item.LastCol)
                        {
                            int offset;
                            for (offset = 1; offset < 100; offset++)
                            {
                                cell = _sheet.GetRow(rowNum + offset).GetCell(item.FirstCol);
                                if (cell != null && cell.CellType != CellType.Blank)
                                    break;
                            }
                            item.Children.AddRange(GenExcelHeaders(item.FirstCol, item.LastCol, rowNum + offset));
                        }

                    }

                }
                else
                {
                    var item = new ExcelHeader() { HeaderText = head };
                    result.Add(item);
                    item.FirstCol = i;
                    item.LastCol = i;
                }
            }
            return result;
        }

        /// <summary>
        /// 将头部输出至工作表中
        /// </summary>
        /// <remarks>
        /// 导出模板和导出数据时，都会用到
        /// </remarks>
        /// <param name="items">头部数据集合</param>
        /// <param name="rowNum">行号</param>
        /// <param name="columnNum">列号</param>
        private void WriteHeadersToSheet(IEnumerable<ExcelHeader> items, int rowNum, int columnNum)
        {
            if (items == null || items.Count() == 0)
                return;
            var maxHeight = items.Max(i => i.Height);

            foreach (var item in items)
            {
                var row = _sheet.GetRow(rowNum);
                if (row == null)
                {
                    row = _sheet.CreateRow(rowNum);
                }
                var cell = row.CreateCell(columnNum);
                cell.CellStyle = GetCellStyle(_workbook, "Header");
                cell.SetCellValue(item.HeaderText);

                if (!string.IsNullOrWhiteSpace(item.Comment))
                {
                    cell.CellComment = GetCellComment(_sheet, 5, 8, item.Comment, "someone");
                }

                var height = maxHeight - item.Height + 1;

                if (height != 1 || item.Width != 1)
                {
                    _sheet.AddMergedRegion(new CellRangeAddress(rowNum, rowNum + height - 1, columnNum, columnNum + item.Width - 1));
                }


                if (item.ValidationData != null && item.ValidationData.Length > 1)
                {
                    SetDataValidation(_sheet, rowNum + maxHeight, 10000, columnNum, columnNum + item.Width - 1, item.ValidationData);
                }

                WriteHeadersToSheet(item.Children, rowNum + height, columnNum);

                columnNum += item.Width;
            }
        }

        /// <summary>
        /// 通过列头数据生成字典
        /// </summary>
        /// <param name="headers">列头数据集合</param>
        /// <returns></returns>
        private Dictionary<string, int> GenDicByHeader(List<ExcelHeader> headers)
        {
            Dictionary<string, int> dict = new Dictionary<string, int>();

            foreach (var header in headers)
            {
                if (header.Children.Count == 0)
                {
                    dict.Add(header.HeaderText, header.FirstCol);
                }
                foreach (var keyValue in GenDicByHeader(header.Children))
                {
                    dict.Add($"{header.HeaderText}_{keyValue.Key}", keyValue.Value);
                }
            }

            return dict;
        }

        /// <summary>
        /// 将集合数据写入表格中
        /// </summary>
        /// <param name="items"></param>
        private void WriteDatasToSheet(IEnumerable items)
        {
            var headers = GenExcelHeaders(items);
            WriteHeadersToSheet(headers, 0, 0);

            _headerPath_to_columnIndex = GenDicByHeader(headers);

            int i = headers.Max(h => h.Height);

            foreach (var item in items)
            {
                _row = _sheet.CreateRow(i++);
                WriteDataToSheet(item, null);
            }
        }

        /// <summary>
        /// 将数据写入表格中
        /// </summary>
        /// <param name="item"></param>
        /// <param name="parentName"></param>
        private void WriteDataToSheet(object item, string parentName)
        {
            if (item == null)
                return;
            var type = item.GetType();

            foreach (var prop in type.GetProperties())
            {
                if (Attribute.GetCustomAttribute(prop, typeof(ExcelHeaderAttribute)) is ExcelHeaderAttribute attr)
                {
                    var header = string.IsNullOrWhiteSpace(parentName) ? attr.Header : $"{parentName}_{attr.Header}";
                    if (prop.PropertyType.Name == "Dictionary`2")
                    {
                        var types = prop.PropertyType.GetGenericArguments();
                        var dict = prop.GetValue(item, null) as IDictionary;

                        if (types[1] == typeof(string) || types[1].IsValueType)
                        {
                            foreach (DictionaryEntry entry in dict)
                            {
                                var subHeader = $"{header}_{entry.Key}";
                                _row.CreateCell(_headerPath_to_columnIndex[subHeader]).SetCellValue(entry.Value?.ToString());
                            }
                        }
                        else
                        {
                            foreach (DictionaryEntry entry in dict)
                            {
                                var subHeader = $"{header}_{entry.Key}";
                                WriteDataToSheet(entry.Value, subHeader);
                            }
                        }
                    }


                    else if (prop.PropertyType == typeof(string) || prop.PropertyType.IsValueType)
                    {
                        _row.CreateCell(_headerPath_to_columnIndex[header]).SetCellValue(prop.GetValue(item, null)?.ToString());
                    }

                    else
                    {
                        WriteDataToSheet(prop.GetValue(item, null), header);
                    }
                }
            }


        }

        /// <summary>
        /// 获取列头的最右方的序号
        /// </summary>
        /// <returns></returns>
        private int GetTotalHeaderLastColumn()
        {
            int index = 0;
            var row = _sheet.GetRow(0);

            var mergeDict = new Dictionary<int, int>();
            for (int i = 0; i < _sheet.NumMergedRegions; i++)
            {
                var region = _sheet.GetMergedRegion(i);
                if (!mergeDict.ContainsKey(region.FirstColumn))
                    mergeDict.Add(region.FirstColumn, region.LastColumn);
            }

            var lastCell = row.Cells.LastOrDefault(c => c.CellType != CellType.Blank && c.CellType != CellType.Error && c.CellType != CellType.Unknown);

            if (lastCell.IsMergedCell)
            {
                index = mergeDict[lastCell.ColumnIndex];
            }
            else
            {
                index = lastCell.ColumnIndex;
            }
            return index;
        }

        /// <summary>
        /// 通过类型生成数据
        /// </summary>
        /// <param name="type"></param>
        /// <param name="parentName"></param>
        /// <returns></returns>
        private object GenData(Type type, string parentName)
        {
            //反射创建该类型的对象
            var obj = Activator.CreateInstance(type);

            /* 
             * 遍历各个属性，并进行判断赋值
             * 1. 属性需要有 ExcelHeader 属性
             * 2. 若属性是集合类型，则必须是 List<> 类
             * 3. 
             */
            foreach (var prop in type.GetProperties())
            {
                if (Attribute.GetCustomAttribute(prop, typeof(ExcelHeaderAttribute)) is ExcelHeaderAttribute attr)
                {
                    if (string.Equals(prop.PropertyType.Name, "Dictionary`2"))
                    {
                        var header = string.IsNullOrWhiteSpace(parentName) ? attr.Header : $"{parentName}_{attr.Header}";

                        var genArgTypes = prop.PropertyType.GetGenericArguments();
                        var dicType = typeof(Dictionary<,>).MakeGenericType(genArgTypes);
                        var dicObj = Activator.CreateInstance(dicType) as IDictionary;

                        bool isClass = !(genArgTypes[1] == typeof(string) || genArgTypes[1].IsValueType);
                        Regex regex;
                        if (!isClass)
                        {
                            regex = new Regex($@"^{header}_(?<name>\S+)");
                        }
                        else
                        {
                            regex = new Regex($@"^{header}_(?<name>\S+?)_");
                        }

                        List<string> names = new List<string>();

                        foreach (var d in _headerPath_to_columnIndex.Keys)
                        {
                            var match = regex.Match(d);
                            if (match.Success)
                            {
                                names.Add(match.Groups["name"].Value);
                            }
                        }

                        foreach (var name in names.Distinct())
                        {
                            object data = null;

                            if (isClass)
                            {
                                data = GenData(genArgTypes[1], $"{header}_{name}");
                            }
                            else
                            {
                                data = GetCellValue(_row.GetCell(_headerPath_to_columnIndex[$"{header}_{name}"], MissingCellPolicy.CREATE_NULL_AS_BLANK));
                                try
                                {
                                    data = Convert.ChangeType(data, genArgTypes[1]);
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine($"GenData error. {ex.Message}. property : {prop.Name}; data : {data}");
                                }
                            }

                            dicObj.Add(name, data);
                        }
                        prop.SetValue(obj, dicObj, null);
                    }
                    else
                    {
                        var header = string.IsNullOrWhiteSpace(parentName) ? attr.Header : $"{parentName}_{attr.Header}";
                        if (prop.PropertyType == typeof(string) || prop.PropertyType.IsValueType)
                        {
                            object data = null;

                            try
                            {
                                data = GetCellValue(_row.GetCell(_headerPath_to_columnIndex[header], MissingCellPolicy.CREATE_NULL_AS_BLANK));
                                data = Convert.ChangeType(data, prop.PropertyType);
                                prop.SetValue(obj, data, null);
                            }
                            catch (Exception ex)
                            {
                                Console.WriteLine($"GenData error. {ex.Message}. property : {prop.Name}; data : {data}");
                            }
                        }
                        else
                        {
                            type = prop.PropertyType;
                            var subObj = GenData(type, header);
                            prop.SetValue(obj, subObj, null);
                        }
                    }
                }
            }

            return obj;
        }

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="cell">指定单元格</param>
        /// <returns></returns>
        protected override object GetCellValue(ICell cell)
        {
            // 合并单元格只有左上角的单元格有值，所有其他单元格则直接跳过
            if (cell.IsMergedCell)
            {
                for (int i = 0; i < _sheet.NumMergedRegions; i++)
                {
                    var region = _sheet.GetMergedRegion(i);
                    if (region.FirstColumn > cell.ColumnIndex || region.LastColumn < cell.ColumnIndex || region.FirstRow > cell.RowIndex || region.LastRow < cell.RowIndex)
                        continue;
                    return base.GetCellValue(_sheet.GetRow(region.FirstRow).GetCell(region.FirstColumn, MissingCellPolicy.CREATE_NULL_AS_BLANK));
                }
                return null;
            }
            else
            {
                return base.GetCellValue(cell);
            }


        }

        /// <summary>
        /// excel 表的数据项是否匹配指定的类
        /// </summary>
        /// <returns></returns>
        private bool ExcelHeader_Fit_ClassProperty()
        {
            var excelHeaders = _headerPath_to_columnIndex.Keys.ToList();

            var type = typeof(T);
            var inst = Activator.CreateInstance(type);
            var classProps = (type.GetMethod("GetHeaderProperty").Invoke(inst, null) as Dictionary<string, string>).Keys.ToList();

            var matchCount = classProps.Intersect(excelHeaders).Count();

            return matchCount > 0;
        }
    }
}
